In [1]:
import pandas as pd
import numpy as np
from pandas.api.types import CategoricalDtype
from plotnine import *
%matplotlib inline
import matplotlib.pyplot as plt
from unicodedata import normalize
In [2]:
import warnings
warnings.filterwarnings("ignore")
I changed the color of markdown cell to light yellow for my comments & thoughts.
In [3]:
df1 = pd.read_csv("/Users/FrancesY/Documents/Columbia/spring2022/5063viz/hw11/assignment-1---winter-olympics-franyang6/data/athletes_and_events.csv")
df2 = pd.read_csv("/Users/FrancesY/Documents/Columbia/spring2022/5063viz/hw11/assignment-1---winter-olympics-franyang6/data/gdp_pop.csv")
df3 = pd.read_csv("/Users/FrancesY/Documents/Columbia/spring2022/5063viz/hw11/assignment-1---winter-olympics-franyang6/data/noc_regions.csv")
In [4]:
df1.head()
Out[4]:
In [5]:
df2 = df2.rename(columns={"Code": "NOC"})
In [6]:
df2.head()
Out[6]:
In [7]:
df3.head()
Out[7]:
1)a) I've decided to merge the three dataframes based on their NOC codes and drop the rows without corresponding NOC codes, due to historical differences on country & NOC names.
After that, I filtered "Medal" to not null, "Season" == "Winter", then dropping rows with duplicated medals in the same event.
Then I grouped the dataframe by "NOC" and sorted value descendingly, the top 10 (NOC_top10) are countries with the most total medals in winter olympics.
In [8]:
df4 = pd.merge(df1, df2, on="NOC")
df5 = pd.merge(df4,df3, on="NOC")
In [9]:
df5 = df5[~df5['Medal'].isnull()]
In [10]:
df5 = df5[df5["Season"] == "Winter"]
In [11]:
df5 = df5.drop_duplicates(
subset = ['Country','Year', 'Event','Medal'],
keep = 'last').reset_index(drop = True)
In [12]:
df5.head()
Out[12]:
In [13]:
NOC_rank = df5.groupby("NOC").size().sort_values(ascending=False)
In [14]:
NOC_top10 = NOC_rank[:10]
NOC_top10
Out[14]:
1)b)¶
In [15]:
# Filtering countries to top10
df6 = df5[df5["NOC"].isin(list(NOC_top10.index))]
In [16]:
n_of_games = df6.groupby("Country").Year.nunique()
n_of_games = n_of_games.reset_index()
n_of_games = n_of_games.rename(columns = {"Year":"Count"})
n_of_games = n_of_games.sort_values("Count",ascending = False)
n_of_games
Out[16]:
In [17]:
(ggplot(n_of_games,
aes(x = "reorder(Country, Count)", y = "Count")) +
geom_bar(stat="identity", fill="#f68060", alpha=.6, width=.4) +
geom_text(aes(label="Count"), color="black", size=8.5)+
labs(x = "Country", title = "How Many Winter Games Each Country Competed In") +
coord_flip() + theme_bw())
Out[17]:
In [18]:
howmany = df6.groupby(["Country","Medal"]).size()
howmany = howmany.reset_index()
howmany = howmany.rename(columns = {0:"Count"})
howmany
Out[18]:
In [19]:
cat_order = ['Gold','Silver','Bronze']
howmany['Medal'] = pd.Categorical(howmany['Medal'], categories=cat_order, ordered=True)
In [20]:
(ggplot(howmany, aes(x = "reorder(Country, Count)", y = "Count", fill = "Medal")) +
geom_bar(stat="identity", alpha=.6, width=.4) +
scale_fill_manual(values = ["#FFD700", "#C0C0C0", "#CD7F32"]) +
labs(x = "Country", title = "How Many Medals of Each Type the Country Won") +
coord_flip() + theme_bw())
Out[20]:
I'm choosing Canada to compare with the States.
In [21]:
df6['Country'] = df6['Country'].str.replace('United States', 'United_States')
In [22]:
df7 = df6[df6["Country"].isin(["United_States","Canada"])]
In [23]:
df7['CountrySex'] = df7['Country'] + df7['Sex']
In [24]:
df8_1 = df7.groupby(["CountrySex","Year"]).size().reset_index()
df8_1 = df8_1.rename(columns = {0:"Count"})
df8_1['Cumulative'] = df8_1.groupby("CountrySex")['Count'].cumsum()
df8_1
Out[24]:
For the first overtime comparison graph I did 4 different columns consisting each sex in each countries: Cumulative Medal Count of female athletes in Canada, males in Canada, females in the States and males in the States.
In [25]:
(ggplot(df8_1, aes(x = "Year", y = "Cumulative", fill = "CountrySex")) +
labs(y = "Cumulative Medal Count", title = "Overtime Comparison 1") +
scale_fill_manual(values = ["#ff0000", "#1261A0", "#801B1B","#00008b"]) +
geom_bar(stat = "identity", width= 3, position = "dodge"))
Out[25]:
In [26]:
df9 = df6.groupby(["Country","Sex","Medal"]).size().reset_index()
df9 = df9.rename(columns = {0:"Count"})
df10 = df9[df9["Country"].isin(["United_States","Canada"])]
df10.reset_index(inplace = True)
df10.drop(columns = "index",inplace=True)
In [27]:
df9.head()
Out[27]:
In [28]:
df10 = df7.groupby(["Country","Year","Sex"]).size().reset_index()
df10 = df10.rename(columns = {0:"Count"})
df10['Cumulative'] = df10.groupby(["Country","Sex"])['Count'].cumsum()
df10.head()
Out[28]:
In [29]:
(ggplot(df10, aes(x = "Year", y = "Cumulative", fill = "Sex")) +
geom_col()+
facet_wrap("Country", scales = "free_x") +
coord_flip() +
labs(y = "Cumulative Medal Count", title = "Overtime Compairson 2") +
theme_bw())
Out[29]:
I created two graphs of cumulative medal count compairson between United States and Canada(overtime compairson 1&2), I would say I prefer the second plot because it's more straight forward in the sense that I used a shared x axis and the readers can tell how many medals each gender got cumulatively overtime between Canada and United States.
2. Medal Counts adjusted by Population, GDP¶
I'm considering adding up the number of medals of different types since a bronze medal is just as "successful" as a gold medal in my opinion. Then I did 3 graphs of top 20 countries with the most medal count unadjusted, medal count adjusted by GDP per capita and medal count adjusted by population.
In [30]:
df13 = df5.groupby("Country").size().sort_values(ascending=False).reset_index()
df13 = df13.rename(columns = {0:"Count"})
In [31]:
df14 = pd.merge(df13, df2, on="Country")
df14["Count_pop"] = df14["Count"]/(df14["Population"])
df14["Count_GDP"] = df14["Count"]/(df14["GDP per Capita"])
df14.head()
Out[31]:
In [32]:
df14_1 = df14.sort_values("Count", ascending= False)[:20]
In [33]:
(ggplot(df14_1, aes(x = "reorder(Country, Count)", y = "Count")) +
geom_bar(stat="identity", fill="#f68060", alpha=.6, width=.4) +
geom_text(aes(label="Count"), color="black", size=8.5)+
labs(x = "Country", title = "Medal Counts Unadjusted Top 20 Countries") +
geom_smooth() +
coord_flip() + theme_bw())
Out[33]:
In [34]:
df14_2 = df14.sort_values("Count_GDP", ascending= False)[:20]
In [35]:
(ggplot(df14_2, aes(x = "reorder(Country, Count_GDP)", y = "Count_GDP")) +
geom_bar(stat="identity", fill="#f68060", alpha=.6, width=.4) +
labs(x = "Country", title = "Medal Counts Adjusted by GDP per Capita Top 20 Countries") +
geom_smooth() +
coord_flip() + theme_bw())
Out[35]:
In [36]:
df14_3 = df14.sort_values("Count_pop", ascending= False)[:20]
In [37]:
(ggplot(df14_3, aes(x = "reorder(Country, Count_pop)", y = "Count_pop")) +
geom_bar(stat="identity", fill="#f68060", alpha=.6, width=.4) +
labs(x = "Country", title = "Medal Counts Adjusted by Population Top 20 Countries") +
geom_smooth() +
coord_flip() + theme_bw())
Out[37]:
In [38]:
# Ranking countries by Unadjusted Count(CountRank), Count adjusted by GDP per capita(GDPRank) and Count adjusted by population (PopRank)
df14['CountRank'] = df14['Count'].rank(method='dense', ascending=False)
df14['GDPRank'] = df14['Count_GDP'].rank(method='dense', ascending=False)
df14['PopRank'] = df14['Count_pop'].rank(method='dense', ascending=False)
df14.head()
Out[38]:
In [39]:
df14_4 = df14[:20]
In [40]:
# Ordering dataframe by Unadjusted Medal Count
ordered_df = df14_4.sort_values(by='Count')
my_range=range(1,len(df14_4.index)+1)
I chose the top 20 countries in total medal rank unadjusted to plot, as the dumbbell plot shows, most of the top 20 countries with total unadjusted medal counts have lower rank in rank adjusted by GDP per capita & rank adjusted by population.
In [41]:
plt.hlines(y=my_range, xmin=0,xmax=40, color='grey', alpha=0.1)
plt.hlines(y=(8,12), xmin=0,xmax=40, color='red', alpha=0.3)
plt.scatter(ordered_df['CountRank'], my_range, color='navy', alpha=1, label='Medal Rank Unadjusted')
plt.scatter(ordered_df['GDPRank'], my_range, color='gold', alpha=0.5 , label='Medal Rank Adjusted by GDP per Capita')
plt.scatter(ordered_df['PopRank'], my_range, color='red', alpha=0.5, label='Medal Rank Adjusted by Population')
plt.legend(bbox_to_anchor=(1.05, 1.0), loc='upper left')
plt.yticks(my_range, ordered_df['Country'])
plt.title("Rank Unadjusted, Adjusted by GDP Per Capita, and Adjusted by Population", loc='left')
plt.xlabel('Rank')
Out[41]:
I then highlighted two countries (China & Russia) with higher population and lower GDP per Capita, and as I thought their ranks in medal rank adjusted by GDP per Capita is higher than their counterparts(other top 20 countries) and their ranks in medal rank adjusted by population is lower than their counterparts as the graph shows.
3. Host Country Advantage¶
In [42]:
df15 = pd.read_html("https://en.wikipedia.org/wiki/List_of_Olympic_Games_host_cities")[1]
host_list = df15[['City.1','Country','Year']]
host_list = host_list.rename(columns = {"City.1":"City"})
In [43]:
df16 = pd.merge(df5, host_list, on=["City","Year"])
df16.head()
Out[43]:
In [44]:
df17 = df16[df16["Country_x"] == df16["Country_y"]]
In [45]:
hosted = df17.groupby(["Country_x","Year"]).size()
hosted = hosted.reset_index()
hosted = hosted.rename(columns = {0:"Count"})
hosted.head()
Out[45]:
In [46]:
hosted1 = df5[df5['Country'].isin(hosted["Country_x"])]
hosted2 = hosted1.groupby(["Country","Year"]).size().reset_index()
hosted2 = hosted2.rename(columns = {0:"Count"})
hosted2.head()
Out[46]:
I plotted line graph of medal count with all the countries that have hosted winter olympics overtime. Then I added black dots for the year each country hosted the winter olympics.
In [47]:
(ggplot(hosted2, aes(x = "Year", y = "Count", color = "Country")) +
geom_line() +
labs(y = "Medal Count", title = "Host Country Advantage") +
theme_bw()) + geom_point(data=hosted, color = "black", size = 2)
Out[47]:
An interesting trend here is that readers can clearly tell many of the dots (medal count of the country for the year countries hosted olympics) were the peak medal count of the decade with immediately declining medal counts the following years. So I would say host country advantage exist.
4. Most successful athletes¶
I calculated the total medal(gold, silver, and bronze) counts of each athlete and selected the top 10
athletes.
In [48]:
successful = df5.groupby(["Name"]).size().sort_values(ascending=False).reset_index()
successful = successful.rename(columns = {0:"Count"})
successful = successful[:10]
In [49]:
(ggplot(successful,
aes(x = "reorder(Name, Count)", y = "Count")) +
geom_bar(stat="identity", fill="#f68060", alpha=.6, width=.4) +
geom_text(aes(label="Count"), color="black", size=8.5)+
labs(x = "Name", y = "Total Medal Count", title = "The Most Successful Winte Olympics Athletes of All Time") +
coord_flip() + theme_bw())
Out[49]:
In [50]:
info = pd.merge(successful, df5, on="Name")
info = info.drop_duplicates(subset=['Name']).reset_index().drop(columns = "index")
In [51]:
info['BMI'] = info['Weight'] / ((info['Height']/100) ** 2)
In [52]:
info['BMI'] = round(info['BMI'], 2)
I then plotted the Body Mass Index of the most successful winter olympics athletes of all time using BMI = weight in kg/(height in m^2) and rounded them up to 2 decimal places. The row for Ivar Eugen Ballangrud is missing since the weight of him is NaN in the dataset and I couldn't find it anywhere on the Internet either.
In [53]:
(ggplot(info,
aes(x = "reorder(Name, Count)", y = "BMI")) +
geom_bar(stat="identity", fill="#f68060", alpha=.6, width=.4) +
geom_text(aes(label="BMI"), color="black", size=8.5)+
labs(title = "Body Mass Index of The Most Successful Winte Olympics Athletes of All Time") +
coord_flip() + theme_bw())
Out[53]:
As the graph shows the top 10 successful winter olympics athletes' BMIs are all in the healthy range of 18 to 25 which is not surprising except only one athlete (Kjetil Andr Aamodt) having a BMI over 25.0 which is considered "overweight" by BMI standard. It shows that the third most successful winter olympic athlete can be considered overweight by society standard which is obviously not an indication of his health and athletic ability.
5. Make two plots interactive¶
In [54]:
import plotly.express as px
import plotly.graph_objs as go
In [55]:
df14.head()
Out[55]:
I plotted an interactive graph in question 2 for all the countries with an winter olympic medal and their GDP per Capita, Total Medal Count, and population. It's clearly more helpful and understandable for a reader since you can select and zoom into the contries and clearly see its population size which was not doable in a static image.
In [56]:
fig = px.scatter(df14, x = "GDP per Capita", y = "Count", size = "Population",
color = "Country",hover_name = "Country",log_x = True, size_max = 100).update_layout(
yaxis_title="Total Medal Count"
)
fig.show()
For the second interactive graph, I used the data in question 4, but instead of the top 10 athletes I filtered to top 500 athletes and sorted the color by the type of sport they play.
In [57]:
athletes = df5.groupby("Name").size().reset_index()
athletes = athletes.rename(columns = {0:"Count"})
athletes = athletes.sort_values("Count", ascending = False)[:500]
In [58]:
info2 = pd.merge(athletes, df5, on="Name")
info2 = info2.drop_duplicates(subset=['Name']).reset_index().drop(columns = "index")
info2['BMI'] = info2['Weight'] / ((info2['Height']/100) ** 2)
info2['BMI'] = round(info2['BMI'], 2)
In [59]:
fig2 = px.scatter(info2, x = "Age", y = "BMI",
color = "Sport", hover_name = "Name",log_x = True).update_layout(
yaxis_title="BMI", title = "BMI of Top 200 Olympic Winter Athletes"
)
fig2.show()
This interactive graph is particularly helpful since it can filter the type of sport the top 100 athletes play and see the BMI differences more clearly. From this interactive graph readers can tell there are certain sports that have medal winning athletes with higher BMIs such as Bobsleigh, with most athletes' BMIs range between 24-32, and certain sports have lower bmi athletes like Ski Jumping have athletes with BMI range between 17-21.
6. Data Table¶
I did the datatable of df5 which is the dataframe with winning medals and added column search features.
In [60]:
df5.to_csv("df5.csv")
In [61]:
%reload_ext rpy2.ipython
In [65]:
%%R
library(DT)
rdf <- read.csv(file = 'df5.csv')
head(rdf)
datatable(rdf, filter = 'top', options = list(
pageLength = 5, autoWidth = TRUE))